import json
import logging
import operator
import sys
import time
from functools import reduce
from typing import Dict, List

from cacheout import Cache
sys.path.extend(["F:/ML/BNC", "D:/BNC", "/www/wwwroot/BNC"])
from Utils import Funcc
import sqlalchemy
from sqlalchemy import and_
from sqlalchemy.ext.declarative import DeclarativeMeta

cache = Cache(maxsize=256, ttl=60 * 6, timer=time.time, default=None)
from StockRelation.Funcc import tag_suffix_remove
from Utils.Funcc import parse_date_from_s, NOT_SPILT_DATE_FORMAT, BASE_DATE_FORMAT, reformat_s_date_s, DateEncoder, \
    parse_rp_2_dicts, parse_rp_2_rows, parse_rp_2_one_rows, filter_and_sort_list
from WebServer.blog.DB.DBValueConstants import ENABLE, DISABLE
from WebServer.blog.Utils.Fnc import wrapper_res_dict, generate_message_dict_4_fail, from_query_set_get_model
from WebServer.blog.models import *

from WebServer.blog.models import DBSession_mysql

log = logging.getLogger(__name__)

count = 0


def toggle_fresh(fresh):
    return FRESH if not fresh == FRESH else NOT_FRESH


def toggle_enable(enable):
    return ENABLE if not enable == ENABLE else DISABLE


def set_sqlalchemy_model_values(obj, field_dic):
    for filed_it_ in field_dic.items():
        if hasattr(obj, filed_it_[0]):
            setattr(obj, filed_it_[0], filed_it_[1])
    return obj


def parse_sqlalchemy_model_2_dict(obj, filter_set={'_sa_instance_state'}):
    _type = type(obj)
    # or issubclass(_type,iterable)
    # 判断是否为many
    many = False
    if (_type == list or _type == tuple):
        many = True
    if not many:
        return _parse_ont_sac_model_2_dict(obj, filter_set=filter_set)
    else:
        res_list = list()
        for one_obj_ in obj:
            if isinstance(type(one_obj_), sqlalchemy.ext.declarative.api.DeclarativeMeta):
                res_list.append(_parse_ont_sac_model_2_dict(one_obj_, 0))
            else:  # 作为普通对象处理
                res_list.append(one_obj_)
        return res_list


def _parse_ont_sac_model_2_dict(obj, depth=0, filter_set={'_sa_instance_state'}):
    _type = type(obj)
    base_type_ = [str, int, float, datetime.date, datetime, dict, ]
    if _type in base_type_:
        return obj
    # if type(obj) == sqlalchemy.util._collections.result:
    #     print(type(obj))
    #     print(obj.id)
    if not hasattr(obj, "__dict__"):
        raise Exception("can't parse type :{}".format(type(obj)))
    res_dic = dict()
    if depth <= 1:
        for it_ in obj.__dict__.items():
            if it_[0] in filter_set or it_[0].startswith("_"):
                continue
            # 递归调用
            if isinstance(type(it_[1]), sqlalchemy.ext.declarative.api.DeclarativeMeta):
                # if isinstance(it_[1], DeclarativeMeta):
                # if issubclass(type(it_[1]), DeclarativeMeta):
                res_dic[_parse_ont_sac_model_2_dict(it_[0], depth=depth + 1)] = _parse_ont_sac_model_2_dict(it_[1],
                                                                                                            depth=depth + 1)
            else:
                res_dic[it_[0]] = it_[1]
        return res_dic
    return None


def insert_replay():
    """ 插入新的复盘数据 """
    code_ = 'test_sql'
    tag_ = 'test_tag1'
    date_ = '2019-12-12'
    mc_tag_ = 'test_tag1_mc'
    # 先插入tag_rename数据，再插入tag_status数据
    session = DBSession_mysql()
    # session.commit()
    # for i_ in range(1, 10):
    #     tr = TagStatus(tag=tag_)
    #     session.merge(tr)
    #     tr = TagStatus(tag=tag_)
    #     session.merge(tr)
    # session.commit()
    try:
        for i_ in range(1, 10):
            tr = TagRename(origin=tag_, to=mc_tag_)
            # 使用uk进行查询
            tr_result_set = session.query(TagRename).filter(and_(
                TagRename.origin == tag_,
            ))
            tr_db = tr_result_set.first()

            ts = TagStatus(tag=mc_tag_, )
            session.merge(ts)
            # 如果db中不存在数据，直接插入新的数据
            if not tr_db:
                session.merge(tr)
            else:
                # 存在的话就只更新指定的字段，然后进行提交
                update_dict = parse_sqlalchemy_model_2_dict(tr)
                # 进行update时需要先插入关联的数据
                tr_result_set.update(update_dict)
            # 对ts进行操作 tag_status中只有主键，只需要进行merge即可
        # 判断tag_status中是否存在主键 tag_status使用作为主键，不会发生问题
        # 通过主键获取或者通过dk进行更新，tag-rename需要直接进行更新操作，需要保留id,
        # 同时origin和to构成联合独立键，插入时也需要判断是否存在这两个
        # 值 ， 是否能够进行更新需要查看数据库的实现方式
    except Exception as e:
        print(e)
        session.rollback()
    finally:
        session.commit()
        session.close()


class CodeTagRelationMapper:
    @classmethod
    def show_relation_code_list(cls, session):
        rp = session.execute(
            "SELECT code_tag_relation.id, code_tag_relation.`code`, code_tag_relation.tag, code_tag_relation.start_date, code_tag_relation.end_date, code_tag_relation.`enable`, code_tag_relation.storage_date,  tag_rename.`to` FROM code_tag_relation left outer join  tag_rename ON tag_rename.origin = code_tag_relation.tag ")
        return parse_rp_2_dicts(rp)

    @classmethod
    def taggle_ctr_status(cls, pk, session):
        # 使用id进行切换
        obj_db = session.query(CodeTagRelation).filter(CodeTagRelation.id == pk).first()
        obj_db.enable = ENABLE if not (obj_db.enable == ENABLE) else DISABLE

    @classmethod
    def taggle_ctr_fresh_status(cls, pk, session):
        # 使用id进行切换
        obj_db = session.query(CodeTagRelation).filter(CodeTagRelation.id == pk).first()
        obj_db.fresh = FRESH if not (obj_db.fresh == FRESH) else NOT_FRESH

    def update_relation(self):
        pass

    @classmethod
    def insert_or_update_relation(cls, ctr: CodeTagRelation, session, enable_tag=ENABLE, tag_to=None, rename_enable=None):
        """ 按照ctd中的原始标签进行插入，优先使用id, 否则UK code,tag 进行操作 """
        # 还需要对tag进行插入
        tag_to_ = ""
        if not tag_to:
            tag_to_ = ctr.tag
            if not tag_to_:
                tag_to_ = tag_suffix_remove(tag_to_)
        else:
            tag_to_ = tag_to
        TagRenameMapper.insert_or_update_tag_rename(TagRename(origin=ctr.tag, to=tag_to_),
                                                    session, tag_enable=enable_tag, rename_enable=rename_enable)

        # TagStatusMapper.insert_tag_status(TagStatus(tag=ctr.tag, enable=enable_tag), session)
        if ctr.id:
            session.merge(ctr)
        else:
            # 按照code,tag进行更新，否者插入, 更新不会改变主键
            query_set = session.query(CodeTagRelation).filter(and_(CodeTagRelation.code == ctr.code,
                                                                   CodeTagRelation.tag == ctr.tag))
            if query_set.first():
                ctr_db = query_set.first()
                set_sqlalchemy_model_values(ctr_db, parse_sqlalchemy_model_2_dict(ctr))
                query_set.update(parse_sqlalchemy_model_2_dict(ctr, ))
            else:
                # 不存在任何键冲突，直接进行插入操作, 需要返回对应的值
                session.add(ctr)
                # session.flush()
                # 暂时提交事务 保证数据已经持久化
                # session.commit()
                return ctr
                pass


    @classmethod
    def pre_p_batch_str(cls, batch_content: str, ):
        batch_content += "\n"
        batch_content = batch_content.replace("\r\n", "\n").replace("\r", "\n")
        batch_contents = batch_content.split("\n")

        ctd_s = []
        for row_ in batch_contents:
            if row_.strip() == "":
                continue
            ctd_cell = CodeTagDate()
            cells = row_.split("|")
            ctd_cell.code = cells[0]
            ctd_cell.tag = cells[1]
            ctd_cell.date = cells[2]
            ctd_cell.source = cells[3]
        return ctd_s

    @classmethod
    def pre_parse_relation_batch(cls, batch_content: str, ) -> list:
        """
            code
            tag
            storage_date
            start_date
            end_date
            reason
            link
            source

            code|tag|start_date|reason|link|source
        """
        # 开启标签重命名
        # 这是网页传入的数据，需要设置为默认启用, 对于其他不同的输入源 另说
        ctr_obj = CodeTagRelation(enable=ENABLE)

    @classmethod
    def insert_relation_batch(cls, ctrs: List[CodeTagRelation], session, enable_ctr=None):
        """
            对数据进行解析
            code|tag|start_date|end_date
            使用循环遍历插入 insert_relation_batch, 事务默认为一次请求一个事务
        """
        for ctr_ in ctrs:
            if not enable_ctr == None:
                ctr_.enable = enable_ctr
            cls.insert_or_update_relation(ctr_, session, rename_enable=ENABLE)
        # for one_ctr_ in ctrs:
        #     cls.insert_or_update_relation(from_query_set_get_model(one_ctr_, CodeTagRelation), session)

    @classmethod
    def delete_relation(cls, d_: CodeTagRelation, session):
        """ 仅仅禁用关系 """
        pk = d_.id
        db_obj = session.query(CodeTagRelation).filter(CodeTagRelation.id == pk).first()
        db_obj.enable = DISABLE
        db_obj.del_reason = d_.del_reason
        # if db_obj:
        #     session.delete(db_obj)


class ReplayMapper:
    @classmethod
    def replay_list(cls, date_, session) -> list:
        """ 需要对标签进行进行重命名和归类显示 """
        rp = session.execute(
            "SELECT code_tag_date.id, code_tag_date.`code`, code_tag_date.`name`, code_tag_date.tag, code_tag_date.date, code_tag_date.source, code_tag_date.`enable`, code_tag_date.storage_date, code_tag_date.fresh, tag_rename.origin, tag_rename.`to` FROM code_tag_date INNER JOIN tag_rename ON code_tag_date.tag = tag_rename.origin where code_tag_date.date=:date",
            {"date": date_})
        return parse_rp_2_dicts(rp)

    @classmethod
    def insert_new_crawl_replay(cls, ctd: CodeTagDate, ctr: CodeTagRelation, session, rename_enable=DISABLE,
                                tag_to=None,
                                copy_link=False):
        # 设置为新数据
        ctd.fresh = FRESH
        ctr.link = ctd.link
        cls.insert_new_replay(ctd, ctr, session, rename_enable, tag_to, copy_link)

    @classmethod
    def insert_new_replay(cls, ctd: CodeTagDate, ctr: CodeTagRelation, session, rename_enable=DISABLE, tag_to=None,
                          copy_link=False):
        """
            插入新的数据后还需要向code_tag_relation中也插入新的数据
            插入新的复盘数据， 也需要建立股票和标签的关联
        """

        # 开始赋值属性
        ctr.code = ctd.code
        ctr.tag = ctd.tag
        ctr.source = ctd.source
        ctr.reason = ctd.reason
        if copy_link:
            ctr.link = ctd.link
        CodeTagRelationMapper.insert_or_update_relation(ctr, session, enable_tag=ENABLE, tag_to=tag_to,
                                                        rename_enable=rename_enable)

        # 如果包含主键，进行更新
        if ctd.id:
            session.merge(ctd)
        else:
            query_set = session.query(CodeTagDate).filter(and_(CodeTagDate.code == ctd.code,
                                                               CodeTagDate.tag == ctd.tag,
                                                               CodeTagDate.source == ctd.source))
            if query_set.first():
                ctd_db = query_set.first()
                set_sqlalchemy_model_values(ctd_db, parse_sqlalchemy_model_2_dict(ctd))
                # query_set.update(parse_sqlalchemy_model_2_dict(ctr, ))
            else:
                # 不存在任何键冲突，直接进行插入操作
                # 会设置默认值
                session.add(ctd)
                # session.commit()
                return ctd

    # @staticmethod
    # def update_replay(ctd: CodeTagDate, session):
    #     """ 更新复盘的数据，暂时不对 股票-标签 进行关联操作 ，需要重新跑"""
    #     # 直接使用主键进行merge
    #     # warning 因为存在id
    #     TagRenameMapper.insert_or_update_tag_rename(TagRename(origin=ctd.tag, to="{}{}".format(ctd.tag, '_to')),
    #                                                 session)
    #     session.merge(ctd)

    @classmethod
    def toggle_fresh(cls, pk, session):
        db_obj = session.query(CodeTagDate).filter(CodeTagDate.id == pk).first()
        db_obj.fresh = FRESH if db_obj.fresh == NOT_FRESH else FRESH

    @classmethod
    def toggle_replay(cls, pk, session):
        """ 禁用恢复状态 """
        # 只更新一个字段
        db_obj = session.query(CodeTagDate).filter(CodeTagDate.id == pk).first()
        #     不进行提交
        db_obj.enable = ENABLE if db_obj.enable == DISABLE else ENABLE

    @classmethod
    def toggle_replay_fresh_2_check(cls, pk, session, enable=None):
        db_obj = session.query(CodeTagDate).filter(CodeTagDate.id == pk).first()
        db_obj.fresh = NOT_FRESH
        if not enable == None:
            db_obj.enable = enable

    @classmethod
    def ban_replay(cls, pk, session, enable=None):
        cls.toggle_replay_fresh_2_check(pk, session, enable=DISABLE)

    @classmethod
    def set_replay_2_unchecked(cls, pk, session, enable=None):
        db_obj = session.query(CodeTagDate).filter(CodeTagDate.id == pk).first()
        db_obj.fresh = NOT_FRESH
        # db_obj.enable =

    @classmethod
    def batch_rename_replay(cls, pks, session):
        """ 禁用恢复状态 """
        for pk_ in pks:
            cls.toggle_replay(pk_, session)

    @classmethod
    def pre_parse_replay_batch(cls, batch_content) -> Dict[str, str]:
        """ 需要先进行标签重命名操作 """
        """ 
        批量插入数据， 需要提取 code,tag, date ,source
                    需要注意默认的数据信息
        """
        # 只需要进行默认值赋值
        batch_content = batch_content.replace("\r\n", "\n").replace("\r", "\n")
        batch_contents = batch_content.split("\n")
        res_str = ""
        for row_ in batch_contents:
            if row_.strip() == "":
                continue
            cells = row_.split("|")
            new_row = ""
            new_row += cells[0].strip() + "|"
            new_row += cells[1].strip() + "|"

            origin_tag = cells[1].strip()
            tag_to_s = None
            rename_from_db = TagRenameMapper.get_tag_to(origin_tag)
            if (not tag_to_s) and rename_from_db:
                tag_to_s = rename_from_db
            # 去除后缀的操作
            if (not tag_to_s):
                tag_to_s = tag_suffix_remove(origin_tag)
            if (not tag_to_s):
                tag_to_s = origin_tag

            new_row += tag_to_s + "|"
            new_row += cells[2].strip() + "|"
            new_row += cells[3].strip() + "|"

            new_row += cells[2].strip() + "|"
            new_row += str(datetime.date.max) + "|"
            res_str += new_row + "\n"
        return {"data": res_str}

    @classmethod
    def insert_replay_batch(cls, batch_content, session):
        """ 解析数据
            [0] code
            [1] tag
            [2] tag_to
            [3] date
            [4] source
            [5] reason
            [6] link
        """
        ctd_objs = []
        batch_content = batch_content.replace("\r\n", "\n").replace("\r", "\n")
        batch_contents = batch_content.split("\n")
        for bc_ in batch_contents:
            if bc_.strip() == "":
                continue
            cells = bc_.split("|")
            ctd_obj = CodeTagDate()

            ctd_obj.code = cells[0].strip()
            ctd_obj.tag = cells[1].strip()
            ctd_obj.date = cells[3].strip()
            ctd_obj.source = cells[4].strip()
            ctd_obj.enable = ENABLE
            ctd_obj.fresh = NOT_FRESH

            ctr_obj = CodeTagRelation()
            ctr_obj.enable = ENABLE
            # fixme 开始时间的确定，应该按照复盘数据中最早出现的 ctr_obj.start_date = ctd_obj.date
            ReplayMapper.insert_new_replay(ctd_obj, ctr_obj, session, tag_to=cells[2], rename_enable=ENABLE)

    @classmethod
    def delete_replay(cls, pk, session):
        db_obj = session.query(CodeTagDate).filter(CodeTagDate.id == pk).first()
        db_obj.enable = DISABLE
        # session.delete(db_obj)

    @classmethod
    def select_unchecked_replay(cls):
        session = DBSession_mysql()
        sql = "SELECT CTD.id, CTD.`code`, SB.name as name, CTD.tag AS origin, CTD.date, CTD.source, CTD.`enable`, CTD.storage_date, CTD.fresh, CTD.reason, CTD.link, TR.`to` AS tag FROM code_tag_date AS CTD INNER JOIN tag_rename AS TR ON CTD.tag = TR.origin left outer join stockbasic AS SB ON SB.symbol = CTD.code WHERE TR.`enable` = :enable AND CTD.fresh = :fresh AND CTD.`enable` = :enable ORDER BY CTD.code"
        rp = session.execute(sql, {"enable": ENABLE, "fresh": FRESH})
        return parse_rp_2_dicts(rp)


class TagRenameMapper:
    @classmethod
    def toggle_tag_rename_status(cls, pk, session):
        obj_db = session.query(TagRename).filter(TagRename.id == pk).first()
        obj_db.enable = ENABLE if not (obj_db.enable == ENABLE) else DISABLE

    @classmethod
    def toggle_tag_map_fresh(cls, pk, session):
        obj_db = session.query(TagRename).filter(TagRename.id == pk).first()
        obj_db.fresh = FRESH if not (obj_db.fresh == FRESH) else NOT_FRESH

    @classmethod
    def list(cls, session):
        return session.query(TagRename).all()

    @classmethod
    def insert_or_update_tag_rename(cls, tag_rename: TagRename, session, tag_enable=None, rename_enable=None):
        """ 先通过id，再通过 uk origin to """
        # 都需要先进行TagStatus的修改操作
        # 不应该在不能使用的地方 进行enable状态的变幻
        # alter 需要查看未赋值的属性被修改为了新的属性 默认保持原有的标签状态
        # alter 只能修改to的属性，不能修改from 属性
        TagStatusMapper.insert_tag_status(TagStatus(tag=tag_rename.to, enable=tag_enable), session)
        # raise Exception("own exception for Transaction")
        if not rename_enable == None:
            tag_rename.enable = rename_enable
        if tag_rename.id:
            # 进行状态的更新操作
            tr_db = session.query(TagRename).get(tag_rename.id)
            update_di = parse_sqlalchemy_model_2_dict(tag_rename, )
            set_sqlalchemy_model_values(tr_db, update_di)
            # session.merge(tr_db)
            # fixme 暂时不知道会不会自动不同属性的操作
        else:
            # 按照origin 和to进行查询
            tr_db = session.query(TagRename).filter(
                and_(TagRename.origin == tag_rename.origin, TagRename.to == tag_rename.to, )).first()
            if tr_db:
                # 进行属性的修改，包括stat
                # session.merge(set_sqlalchemy_model_values(tr_db, parse_sqlalchemy_model_2_dict(tag_rename)))
                global count
                count += 1
                print("count : {} ,dict:{}".format(count, parse_sqlalchemy_model_2_dict(tag_rename)))
                set_sqlalchemy_model_values(tag_rename, parse_sqlalchemy_model_2_dict(tag_rename))
            else:
                session.add(tag_rename)
                # assert "should not call in there bug special toggle method "
            # assert tr_db, "不能出现对象的origin and to  | id 都不匹配的情况，"

    @classmethod
    def delete_tag_rename(cls, pk, session):
        db_obj = session.query(TagRename).filter(TagRename.id == pk).first()
        session.delete(db_obj)

    def update_tag_map(self):
        pass

    def update_tag_map_batch(self):
        pass

    def insert_tag_map(self):
        pass

    @classmethod
    def get_tag_to(cls, tag_origin):
        """ 自己创建session， 查询对应的tag_origin的数据 """
        session = DBSession_mysql()
        obj = session.query(TagRename).filter(TagRename.origin == tag_origin).first()
        if obj:
            return obj.to
        return None

    @classmethod
    def pre_parse_replay_batch(cls, pks, to_item, session):
        """ """
        pass

    @classmethod
    def batch_update_col(cls, pks, to_item, session):
        """
            只选取部分参数进行修改操作
            需要修改的属性为to
            对于当前的变更操作：需要删除原有的映射，但需要保留原有的映射状态
        """
        # fixme 是否需要删除tag_status中的状态
        # 只更新to
        for pk_ in pks:
            tr_up_ = TagRename(id=pk_, to=to_item['to'])
            # cls.delete_tag_rename(pk_, session)
            cls.insert_or_update_tag_rename(tr_up_, session, tag_enable=ENABLE)

    @classmethod
    def only_to_tag(cls, session,kw):
        rp = session.execute("SELECT distinct tag_rename.`to` FROM tag_rename INNER JOIN tag_status ON tag_rename.`to` = tag_status.tag where tag_rename.`enable` = 1 and tag_status.`enable` = 1 and tag_rename.`to` like :kw",
                             {"kw":"%{}%".format(kw)})
        return parse_rp_2_one_rows(rp)

    @classmethod
    def child_tags(cls, parent_tag, session):
        rp = session.execute("SELECT distinct tag_rename.origin FROM tag_rename INNER JOIN tag_status ON tag_rename.`to` = tag_status.tag where tag_rename.`enable` = 1 and tag_status.`enable` = 1 and tag_rename.`to` = :to",
                             {"to":parent_tag})
        return parse_rp_2_one_rows(rp)

    @classmethod
    def delete_tag_rename_by_uk(cls, tr:TagRename, session):
        db_obj = session.query(TagRename).filter(and_(TagRename.origin==tr.origin, TagRename.to == tr.to)).first()
        if db_obj:
            db_obj.fresh = NOT_FRESH
            db_obj.enable = DISABLE

class TagStatusMapper:
    @classmethod
    def insert_tag_status(cls, tagStatus: TagStatus, session):
        """ insert or update """
        db_obj = session.query(TagStatus).filter(TagStatus.tag == tagStatus.tag).first()
        if db_obj:
            set_sqlalchemy_model_values(db_obj, parse_sqlalchemy_model_2_dict(tagStatus))
        else:
            session.add(tagStatus)

    @classmethod
    def toggle_tag_status(cls, tag, session):
        obj_db = session.query(TagStatus).filter(and_(TagStatus.tag == tag)).first()
        obj_db.enable = ENABLE if not (obj_db.enable == ENABLE) else DISABLE

    @classmethod
    def list(cls, session):
        return session.query(TagStatus).all()


class SearchCTRKWMapper:
    @classmethod
    @cache.memoize(ttl=60 * 1)
    def tag_desc(cls):
        """ 进行标签排序的返回和缓存 """
        session = DBSession_mysql()
        rp = session.execute(
            "select A.to from (SELECT distinct CTR.code,TR.to,count(*) AS cnt FROM tag_status AS TS INNER JOIN tag_rename AS TR ON TR.`TO` = TS.tag INNER JOIN code_tag_relation AS CTR ON CTR.tag = TR.origin WHERE TS.ENABLE = 1 GROUP BY TR.to order by cnt DESC)AS A")
        res = reduce(operator.add, parse_rp_2_rows(rp))
        session.close()
        return res

    @classmethod
    def search_tags(cls, kws):
        res_set = set()
        for kw_ in kws:
            res_set.update((set(filter(lambda it_: it_.find(kw_) > -1, cls.tag_desc()))))
        return sorted(list(res_set))

    @classmethod
    def search_kw_(cls, kws):
        session = DBSession_mysql()
        combine_res_ = []
        res = []
        try:
            res.extend(filter_and_sort_list(cls.tag_desc(),  kws))
            rp = session.execute("""SELECT DISTINCT CONCAT(CTR.CODE,'(code)') FROM stockbasic AS SB RIGHT OUTER JOIN ( SELECT DISTINCT CTR1.`code` FROM code_tag_relation AS CTR1 ) AS CTR ON SB.symbol = CTR. CODE""")
            res.extend(filter_and_sort_list(parse_rp_2_one_rows(rp), kws))
            # print(combine_res_)
        finally:
            session.close()
        # for kw_ in kws:
        #     res_set.update((set(filter(lambda it_: it_.find(kw_) > -1, combine_res_))))
        return res



    @classmethod
    def ctr_from_code(cls, code):
        session = DBSession_mysql()
        try:
            rp = session.execute(
                "SELECT CTR.id, CTR.`code`,SB.name AS name, CTR.tag AS origin, CTR.start_date, CTR.end_date, CTR.storage_date, CTR.reason, CTR.link, TS.tag AS tag FROM code_tag_relation AS CTR INNER JOIN tag_rename AS TR ON CTR.tag = TR.origin INNER JOIN tag_status AS TS ON TS.tag = TR.`to` AND TS. ENABLE = 1 AND TR. ENABLE = 1 AND CTR. ENABLE = 1 LEFT JOIN stockbasic AS SB ON SB.symbol = CTR.code WHERE  CTR.`code` = :code;",
                {"code": code})
            # select_rela_tags_sql = "SELECT code as code , IFNULL(SB.name,'默认股票名') as name ,cur_up_times as cur_up_times,cur_date as cur_date,cur_count as cur_count , (cur_up_times / IF(cur_count = 0,-1, cur_count)) AS rela_ratio from {} left outer join {} AS SB ON SB.symbol = code INNER JOIN code_tag_relation AS CTR INNER JOIN tag_rename AS TR ON CTR.tag = TR.origin INNER JOIN tag_status AS TS ON TS.tag = TR.`to` AND TS. ENABLE = 1 AND TR. ENABLE = 1 AND CTR. ENABLE = 1 LEFT JOIN stockbasic AS SB ON SB.symbol = CTR.code where code = %s order by cur_up_times DESC, rela_ratio DESC".format(

            return parse_rp_2_dicts(rp)
        finally:
            session.close()

    @classmethod
    def ctr_from_tag(cls, tag):
        session = DBSession_mysql()
        try:
            rp = session.execute(
                "SELECT CTR.id, CTR.`code`,SB.name AS name, CTR.tag AS origin, CTR.start_date, CTR.end_date, CTR.storage_date, CTR.reason, CTR.link, TS.tag AS tag FROM code_tag_relation AS CTR INNER JOIN tag_rename AS TR ON CTR.tag = TR.origin INNER JOIN tag_status AS TS ON TS.tag = TR.`to` AND TS. ENABLE = 1 AND TR. ENABLE = 1 AND CTR. ENABLE = 1 LEFT JOIN stockbasic AS SB ON SB.symbol = CTR.code WHERE TS.tag=:tag",
                {"tag": tag})
            return parse_rp_2_dicts(rp)
        finally:
            session.close()

    @classmethod
    def ctr_from_id(cls, id):
        session = DBSession_mysql()
        try:
            rp = session.execute(
                "SELECT CTR.id, CTR.`code`,SB.name AS name, CTR.tag AS origin, CTR.start_date, CTR.end_date, CTR.storage_date, CTR.reason, CTR.link, TS.tag, CTR.source AS tag FROM code_tag_relation AS CTR INNER JOIN tag_rename AS TR ON CTR.tag = TR.origin INNER JOIN tag_status AS TS ON TS.tag = TR.`to` AND TS. ENABLE = 1 AND TR. ENABLE = 1 AND CTR. ENABLE = 1 LEFT JOIN stockbasic AS SB ON SB.symbol = CTR.code WHERE CTR.id= :id",
                {"id": id})
            return parse_rp_2_dicts(rp)
        finally:
            session.close()


def insert_replay_bu():
    session = DBSession_mysql()
    rp_mapper = ReplayMapper()
    date_ = datetime.date.today()
    try:
        # ctr = CodeTagRelation(code='a',tag,)
        rp_mapper.insert_new_replay(
            CodeTagDate(code='test_', tag='test_tag_sqlalchemy_update', source='test', date=date_),
            CodeTagRelation(code='test_', tag='test_tag_sqlalchemy_update'), session)
    except Exception as e:
        session.rollback()
        print(e)
    finally:
        session.commit()
        session.close()


def main():
    # insert_replay()
    # insert_replay_bu()
    session = DBSession_mysql()
    # insert_tag = '1'
    # obj_db = session.query(TagStatus).filter(TagStatus.tag == insert_tag).first()
    # assert obj_db == None
    # session.merge(TagStatus(tag=insert_tag))
    # obj_db = session.query(TagStatus).filter(TagStatus.tag == insert_tag).first()
    # assert not obj_db == None, '已经插入数据，不因为空'

    # SearchCTRKWMapper.search_kw_(session)
    # with open("G:/replay.txt", "r", encoding='utf8') as f:
    #     a = f.readlines()
    #     a_s = ""
    #     for line_ in a:
    #         a_s += "{}\n".format(line_)
    #     ReplayMapper.insert_replay_batch(a_s, session)
    ctd_ = CodeTagDate()
    ctd_.code = 'test_code_1'
    ctd_.tag = 'test_code_1'
    ctd_.date = '2020-1-1'
    ctd_.enable = ENABLE
    ctd_.source = '测试插入'
    ctd_.link = 'http://www.baidu.com'
    ReplayMapper.insert_new_replay(ctd_, CodeTagRelation(enable=ENABLE), session, rename_enable=ENABLE, )
    session.commit()
    session.close()


#

if __name__ == '__main__':
    main()
